/*
 * $RCSfile: PostgreSQLConnection.java,v $
 * $Revision: 1.28 $
 *
 * Comments:
 *
 * (C) Copyright Biuro-Technika 2004.  All rights reserved.
 * THIS IS UNPUBLISHED PROPRIETARY SOURCE CODE OF Biuro-Technika
 * The copyright notice above does not evidence any
 * actual or intended publication of such source code.
 *
 * $Author: michal $          $Locker:  $
 * $Date: 2010/06/13 07:42:13 $
 * $Log: PostgreSQLConnection.java,v $
 * Revision 1.28  2010/06/13 07:42:13  michal
 * optymalizacja petli
 *
 * Revision 1.27  2010/04/26 11:12:49  michal
 * dodanie pol current_timestamp oraz current_date
 *
 * Revision 1.26  2010/04/13 09:35:54  michal
 * parametryzacja kodu
 *
 * Revision 1.25  2010/02/25 10:34:15  mdettla
 * Poprawienie metody datetimeExtract.
 *
 * Revision 1.24  2010/02/24 14:32:39  mdettla
 * Dodanie metody datetimeExtract.
 *
 * Revision 1.23  2010/02/16 15:14:38  michal
 * pobieranie listy schem z poprawna wielkoscia znakow
 *
 * Revision 1.22  2010/02/01 13:27:12  michal
 * poprawa metody pobierajacej schemy
 *
 * Revision 1.21  2010/01/29 14:32:42  michal
 * uporzadkowanie kodu
 *
 * Revision 1.20  2009/10/09 08:35:00  michal
 * domyslne typy sql na statycznej zmiennej
 *
 * Revision 1.19  2009/09/22 11:46:35  michal
 * ParaSoft -> Biuro-Technika
 *
 * Revision 1.18  2009/05/25 08:00:30  michal
 * dodanie implementacji metody alterColumnType
 *
 * Revision 1.17  2009/05/15 07:10:07  michal
 * nadpisanie metody getFullTableColumn, poprawa zduplikowanych metod
 *
 * Revision 1.16  2009/04/28 10:40:22  michal
 * komentarz do appendQueryLimit
 *
 * Revision 1.15  2008/12/09 08:22:55  michal
 * dodanie metod dropColumn oraz sTempSelect
 *
 * Revision 1.14  2008/03/28 18:48:03  michal
 * zamiana date_trunc na date w celu przyspieszenia wykonywania zapytan
 *
 * Revision 1.13  2008/03/06 12:04:45  muzzy
 * organizacja importow
 *
 * Revision 1.12  2007/11/13 12:30:38  muzzy
 * nowa existsFunction
 *
 * Revision 1.11  2007/10/25 14:37:33  muzzy
 * poprawki w indexach, round i numericCast
 *
 * Revision 1.10  2007/08/24 09:09:05  dominik
 * Implementacja dodatkowych metod
 *
 * Revision 1.9  2007/01/16 15:50:55  dominik
 * Zamiana PreparedStatement na DebuggablePreparedStatement - debugowanie zapytania
 *
 * Revision 1.8  2007/01/10 09:19:28  dominik
 * Usuniecie dropowania funkcji
 *
 * Revision 1.7  2007/01/08 11:12:37  dominik
 * Sprawdzanie czy istnieje schema centralna i czy istnieje jezyk plpgsql
 *
 * Revision 1.6  2006/01/31 15:39:00  muzzy
 * tworzenie i sprawdzanie istnienia funkcji
 *
 * Revision 1.5  2005/10/13 14:15:29  muzzy
 * triggery, funkcje i left join
 *
 * Revision 1.4  2005/01/18 15:01:03  limbur
 * Dodano funkcje bitand(...)
 *
 * Revision 1.3  2005/01/05 17:57:07  limbur
 * Uwzgledniono kolejne roznice pomiedzy Oracle - PostgreSQL
 *
 * Revision 1.2  2004/12/17 17:11:20  limbur
 * PostgreSQL - kolejne zmiany
 *
 * Revision 1.1  2004/12/15 08:41:07  limbur
 * Wersja pod PostgreSQL
 *
 */

package parasoft.centrap.connection;

import static parasoft.centrap.utils.Error.sql_seq_currval;
import static parasoft.centrap.utils.Error.sql_seq_next;
import static parasoft.centrap.utils.Message.sql_schema_create;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import parasoft.centrap.migration.Column;
import parasoft.centrap.migration.Index;

public class PostgreSQLConnection extends DbConnection
{
	private static List<JdbsType> standartTypes = setStandardTypes();
	
    public PostgreSQLConnection(final DbConnectionBroker broker) throws SQLException
    {
        this(broker, false);
    }
    public PostgreSQLConnection(final DbConnectionBroker broker, final Connection conn) throws SQLException
    {
        super(broker, conn);
    }
    public PostgreSQLConnection(final DbConnectionBroker broker, final boolean last) throws SQLException
    {
        super(broker, last);
    }

    @Override
	public void setId(final DebuggablePreparedStatement stmt, final int index, final long value) throws SQLException
    {
        stmt.setString(index, Long.toString(value));
    }

    @Override
	public boolean existsSchema(final String schema) throws SQLException
    {
        final DatabaseMetaData dmd = getConnection().getMetaData();
        ResultSet rs = null;
        try {
            rs = dmd.getSchemas();
            while(rs.next()) {
                if(rs.getString(1).equalsIgnoreCase(schema)) {
                    return true;
                }
            }
            return false;
        } finally {
            if(rs != null) {
                rs.close();
            }
        }
    }

    @Override
	public void dropTable(final String table) throws java.sql.SQLException
    {
        executeUpdate("DROP TABLE " + table + " CASCADE");
    }

    @Override
	public boolean existsTable(final String table) throws SQLException
    {
        final DatabaseMetaData dmd = getConnection().getMetaData();
        ResultSet rs = null;
        final String[] aTypes = { "TABLE" };
		final String schema = "";
        try {
            rs = dmd.getTables(null,
                               correct(schema), correct(table),
                               aTypes);
            return rs.next();
        } finally {
            if(rs != null) {
                rs.close();
            }
        }
    }

    @Override
	public boolean existsTable(final String schema, final String table) throws SQLException
    {
        final DatabaseMetaData dmd = getConnection().getMetaData();
        ResultSet rs = null;
        final String[] aTypes = { "TABLE" };
        try {
            rs = dmd.getTables(null,
            		correct(schema), correct(table),
                               aTypes);
            return rs.next();
        } finally {
            if(rs != null) {
                rs.close();
            }
        }
    }

    @Override
	public List<Column> getFullTableColumns(final String schema, final String table)
    		throws SQLException {
    	return super.getFullTableColumns(correct(schema), correct(table));
    }

    @Override
	public List<String> getTableColumns(final String table) throws SQLException
    {
		return getTableColumns("", table);
    }

    @Override
	public List<String> getTableColumns(final String schema, final String table)
        throws SQLException
    {
    	final List<String> columns = new ArrayList<String>();
        final DatabaseMetaData dmd = getConnection().getMetaData();
        ResultSet rs = null;
        try {
            rs = dmd.getColumns(null,
            		correct(schema), correct(table),
                                null);
            while(rs.next()) {
                if(rs.getString(4) != null) {
                    columns.add(rs.getString(4).toUpperCase());
                }
            }
            return columns;
        } finally {
            if(rs != null) {
                rs.close();
            }
        }
    }

    @Override
	public boolean existsTableColumn(final String table, final String column)
        throws SQLException
    {
		return existsTableColumn("", table, column);
    }

    @Override
	public boolean existsTableColumn(final String schema, final String table, final String column)
        throws SQLException
    {
        final DatabaseMetaData dmd = getConnection().getMetaData();
        ResultSet rs = null;
        try {
            rs = dmd.getColumns(null,
            		correct(schema), correct(table),
                                correct(column));
            return rs.next();
        } finally {
            if(rs != null) {
                rs.close();
            }
        }
    }

    @Override
	public boolean existsFunction(final String name) throws java.sql.SQLException
    {
        ResultSet rs = null;
        try {
            final String query = "SELECT * FROM PG_PROC WHERE " +
                "UPPER(PRONAME)=UPPER('" + name + "')";
            rs = executeQuery(query);
            if(rs.next()) {
                return true;
            }
            return false;
        } finally {
            if(rs != null) {
                rs.close();
            }
        }
    }

    @Override
	public boolean existsFunction(final String name, final String functBody) throws java.sql.SQLException
    {
        ResultSet rs = null;
        try {
            final String query = "SELECT * FROM PG_PROC WHERE " +
                "UPPER(PRONAME)=UPPER('" + name + "')";
            rs = executeQuery(query);
            if(rs.next()) {
                final String body = rs.getString("PROSRC");
                if (!body.toUpperCase().equals(functBody.toUpperCase())) {
                    return false;
                } else {
                    return true;
                }
            }
            return false;
        } finally {
            if(rs != null) {
                rs.close();
            }
        }
    }

    @Override
	public void createFunction(final String name, final String functBody) throws java.sql.SQLException
    {
        executeUpdate("CREATE OR REPLACE FUNCTION " + name + "() RETURNS TRIGGER AS '" + functBody +
                      "' language 'plpgsql'");
    }

    @Override
	public void dropFunction(final String name) throws java.sql.SQLException
    {
        executeUpdate("DROP FUNCTION " + name + " CASCADE");
    }

    @Override
	public void createTrigger(final String name, final String tableName, final String functName) throws java.sql.SQLException
    {
        executeUpdate("CREATE TRIGGER " + name + " AFTER INSERT OR UPDATE ON " + tableName +
                      " FOR EACH ROW EXECUTE PROCEDURE " + functName);
    }

    public void dropTrigger(final String name) throws java.sql.SQLException
    {
        executeUpdate("DROP TRIGGER " + name);
    }

    @Override
	public boolean existsTrigger(final String triggerName, final String schema, final String tableName) throws java.sql.SQLException
    {
        ResultSet rs = null;
        try {
            final String query = "SELECT * FROM PG_TRIGGER, PG_CLASS, PG_NAMESPACE WHERE " +
                "PG_TRIGGER.TGRELID=PG_CLASS.OID AND " +
                "PG_CLASS.RELNAMESPACE=PG_NAMESPACE.OID AND " +
                "UPPER(PG_TRIGGER.TGNAME)=UPPER('" + triggerName + "') AND " +
                "UPPER(PG_CLASS.RELNAME)=UPPER('" + tableName + "') AND " +
                "UPPER(PG_NAMESPACE.NSPNAME)=UPPER('" + schema + "')";

            rs = executeQuery(query);
            if(rs.next()) {
                return true;
            }
            return false;
        } finally {
            if(rs != null) {
                rs.close();
            }
        }
    }

    @Override
	public void createSequence(final String key) throws java.sql.SQLException
    {
        executeUpdate("CREATE SEQUENCE " + key +
                      " INCREMENT 1 MINVALUE 1 START 1");
    }

    @Override
	public void createSequence(final String key, final long start) throws java.sql.SQLException
    {
        executeUpdate("CREATE SEQUENCE " + key +
                      " INCREMENT 1 MINVALUE 1 START " + start);
    }

    @Override
	public void dropSequence(final String key) throws java.sql.SQLException
    {
        executeUpdate("DROP SEQUENCE " + key + " CASCADE");
    }

    @Override
	public boolean existsSequence(final String key) throws java.sql.SQLException
    {
        final DatabaseMetaData dmd = getConnection().getMetaData();
        ResultSet rs = null;
        final String[] aTypes = { "SEQUENCE" };
		final String schema = "";
        try {
            rs = dmd.getTables(null,
                               correct(schema), correct(key),
                               aTypes);
            if(rs.next()) {
                return true;
            }
            return false;
        } finally {
            if(rs != null) {
                rs.close();
            }
        }
    }

    @Override
	public boolean existsSequence(final String schema, final String key) throws java.sql.SQLException
    {
        final DatabaseMetaData dmd = getConnection().getMetaData();
        ResultSet rs = null;
        final String[] aTypes = { "SEQUENCE" };
        try {
            rs = dmd.getTables(null,
            		correct(schema), correct(key),
                               aTypes);
            if(rs.next()) {
                return true;
            }
            return false;
        } finally {
            if(rs != null) {
                rs.close();
            }
        }
    }

    @Override
	public String sCurrVal(final String key)
    {
        return "currval('" + key + "')";
    }

    @Override
	public String sNextVal(final String key)
    {
        return "nextval('" + key + "')";
    }

    @Override
	public String round(final String number, final int precision)
    {
        return "round(" + number + "," + precision + ")";
    }

    @Override
	public long currentId(final String key) throws java.sql.SQLException
    {
        ResultSet rs = null;
        try {
            rs = executeQuery("SELECT currval('" + key + "')");
            if(rs.next()) {
                return rs.getLong(1);
            }
            else {
                throw sql_seq_currval.sql(key);
            }
        } finally {
            closeStatement(rs);
        }
    }

    @Override
	public long nextId(final String key) throws java.sql.SQLException
    {
        ResultSet rs = null;
        try {
            rs = executeQuery("SELECT nextval('" + key + "')");
            if(rs.next()) {
                return rs.getLong(1);
            }
            else {
                throw sql_seq_next.sql(key);
            }
        } finally {
            closeStatement(rs);
        }
    }

    @Override
	public void createIndex(final String schema, final String table, final String column) throws java.sql.SQLException
    {
        executeUpdate("CREATE INDEX " + createIndexName(schema, table, column) +
                      " ON " + table + "(" + column + ")");
    }

    @Override
	public void createIndex(final String index_name, final String table, final List<String> columns) throws java.sql.SQLException
    {
        String query = "CREATE INDEX " + index_name +
            " ON " + table + "(" + columns.get(0);
        for(int i = 1, len = columns.size(); i < len; i++) {
            query += "," + columns.get(i);
        }
        query += ")";
        executeUpdate(query);
    }

    @Override
	public void createIndex(final String index_name, final String table, final List<String> columns, final boolean unique) throws java.sql.SQLException
    {
        String query = "CREATE ";
        if(unique) {
            query += " UNIQUE ";
        }
        query +=" INDEX " + index_name +
            " ON " + table + "(" + columns.get(0);
        for(int i = 1, len = columns.size(); i < len; i++) {
            query += "," + columns.get(i);
        }
        query += ")";
        executeUpdate(query);
    }

    @Override
	public void dropIndex(final String schema, final String table, final String column) throws java.sql.SQLException
    {
        executeUpdate("DROP INDEX " + createIndexName(schema, table, column));
    }

    @Override
	public void dropIndex(final String index_name) throws java.sql.SQLException
    {
        executeUpdate("DROP INDEX " + index_name + " CASCADE");
    }

    @Override
	public boolean existsIndex(final String table, final String column) throws SQLException
    {
        final DatabaseMetaData dmd = getConnection().getMetaData();
        ResultSet rs = null;
		final String schema = "";
        try {
            rs = dmd.getIndexInfo(null,
                                  correct(schema), correct(table),
                                  false, false);
            while(rs.next()) {
                if((rs.getString(9) != null) &&
                   rs.getString(9).equalsIgnoreCase(column)) {
                    return true;
                }
            }
            return false;
        } finally {
            if(rs != null) {
                rs.close();
            }
        }
    }

    @Override
	public boolean existsIndex(final String schema, final String table, final String column) throws java.sql.SQLException
    {
        final DatabaseMetaData dmd = getConnection().getMetaData();
        ResultSet rs = null;
        try {
            rs = dmd.getIndexInfo(null,
            		correct(schema), correct(table),
                                  false, false);
            while(rs.next()) {
                if((rs.getString(9) != null) &&
                        rs.getString(9).equalsIgnoreCase(column))
                {
                    return true;
                }
            }
            return false;
        } finally {
            if(rs != null) {
                rs.close();
            }
        }
    }

    @Override
	public boolean existsIndex(final String index_name) throws java.sql.SQLException
    {
        DebuggablePreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = prepareStatement("SELECT relname AS INDEX_NAME" +
                                   " FROM pg_catalog.pg_class,pg_catalog.pg_index" +
                                   " WHERE oid=indexrelid AND relname=?");
           stmt.setString(1, correct(index_name));
           rs = stmt.executeQuery();
           if(rs.next()) {
               return true;
           }
           else {
               return false;
           }
        } finally {
            closeStatement(rs, stmt);
        }
    }

    @Override
	public boolean isOuterJoin()
    {
        return true;
    }

    @Override
	public String leftJoin(final String column, final String condition)
    {
        return " LEFT JOIN " + column + " ON (" + condition + ")";
    }

    /**
     * <li>odaje do zapytania <i>"limit 1"</i>
     * <li>Sprawdza wyst�pienie klauzuli <i>"WHERE"</i> oraz dodaje w przypadku braku
     * @param query
     * @see parasoft.centrap.connection.DbConnection#appendQueryLimit(java.lang.String)
     */
    @Override
	public String appendQueryLimit(final String query)
    {
        return "(" + query + " limit 1)";
    }

    @Override
	public String decodeQuery(final String expression, final List<String> values, final List<String> results)
    {
        String query = "case " + expression;
        int i = 0;
        while(i < values.size()) {
            query += " when " + values.get(i) +
                " then " + results.get(i);
            i++;
        }
        query += " else " + results.get(i) + " end";

        return query;
    }

    @Override
	public String decodeQuery(final String expression, final List<String> values, final List<String> results, final boolean resround)
    {
        String query = "case " + expression;
        int i = 0;
        while(i < values.size()) {
            query += " when " + values.get(i) + " then ";
            if(resround) {
                query += "round(" + results.get(i) + ")";
            }
            i++;
        }
        query += " else " + results.get(i) + " end";

        return query;
    }

    @Override
	public String dateTrunc(final String column)
    {
        return "date(" + column + ")";
    }
    
    @Override
	public String currentTimestamp(){
    	return "current_timestamp";
    }
    @Override
	public String currentDate(){
    	return "current_date";
    }
    
    @Override
	public String datetimeExtract(final String datetimePart, final String column) {
    	return "EXTRACT(" + datetimePart + " FROM " + column + ")";
    }

    @Override
	public String secOfDay(final String column)
    {
        return "cast(to_char(" + column + ", 'SSSS') as integer)";
    }

    @Override
	public String numericCast(final String column)
    {
        return "cast(" + column + " as numeric)";
    }

    @Override
	public String varcharCast(final String column)
    {
        return "cast(" + column + " as varchar)";
    }

    @Override
	public String varcharCast()
    {
        return "cast(? as varchar)";
    }

    @Override
	public String power(final String number, final String exponent)
    {
        return "POW(" + number + "," + exponent + ")";
    }

    @Override
	public String bitand(final String arg1, final String arg2)
    {
        return arg1 + "&" +  arg2;
    }

    @Override
	public String concat(final String str1, final String str2)
    {
        return str1 + "||" +  str2;
    }

    @Override
	public String sTempSelect(final String column)
    {
    	return "select ("+column+") ";
    }

    public void creatLangPLpgSQL() throws SQLException
    {
        System.err.println();
        System.err.print( "Create function plpgsql_call_handler()..." );
        executeUpdate("CREATE FUNCTION plpgsql_call_handler() RETURNS OPAQUE AS "+
                      "'$libdir/plpgsql.so' LANGUAGE 'C'");
        System.err.println( "OK" );

        System.err.print( "Create language plpgsql..." );
        executeUpdate("CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' "+
                      "HANDLER plpgsql_call_handler "+
                      "LANCOMPILER 'PL/pgSQL'");
        System.err.println( "OK" );
    }

    public boolean existsLangPLpgSQL() throws SQLException
    {
        ResultSet rs = null;
        try {
            rs = executeQuery("SELECT lanname FROM pg_language" +
                                    " WHERE lanname='plpgsql'");
            if (rs.next()) {
                return true;
            }
            else {
                return false;
            }
        }
        finally {
            closeStatement(rs);
        }
    }

    @Override
	public void createSchema(final String schemaName) throws SQLException
    {
        executeUpdate("CREATE SCHEMA "+schemaName);
        System.err.println( sql_schema_create.format(schemaName ));
    }

    public boolean existsCentralSchema(final String schemaName) throws SQLException
    {
        DebuggablePreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = prepareStatement("SELECT nspname FROM pg_namespace" +
                                    " WHERE nspname=?");
            stmt.setString(1,schemaName);
            rs = stmt.executeQuery();
            if (rs.next()) {
                return true;
            }
            else {
                return false;
            }
        }
        finally {
            closeStatement(rs, stmt);
        }
    }

	@Override
	public List<String> getSchemas() throws SQLException {
		final List<String> out = new ArrayList<String>();
        final String query = "SELECT NSPNAME, OID ,NSPOWNER,USESYSID FROM PG_NAMESPACE, PG_SHADOW WHERE (USENAME = ?) AND NSPOWNER = USESYSID";
        final PreparedStatement stmt = prepareStatement(query);
		stmt.setString(1, "");

        final ResultSet rs = stmt.executeQuery();
        while (rs.next()){
                final String schema_name = rs.getString("NSPNAME");
                final String nspowner = rs.getString("NSPOWNER");
                final String usesysid = rs.getString("USESYSID");
                if(!nspowner.equals(usesysid)){
                        continue;
                }
//                long oid = rs.getLong("OID");
                out.add(correct(schema_name));
        }
        closeStatement(rs, stmt);
		return out;
	}

	@Override
	public List<Index> getIndexs(final String schema, final String table) throws SQLException {
		// TODO Doda� z  DatabaseMetaData::.getIndexInfo(null, schema, table,false, false); 
		return null;
	}

	@Override
	public void alterColumnType(final String tableName, final String columnName, final String newType) throws java.sql.SQLException{
		executeUpdate( "ALTER TABLE " + tableName + " ALTER COLUMN " +
				columnName + " TYPE " + newType );
	}
	
	private static List<JdbsType> setStandardTypes() {
		standartTypes = new ArrayList<JdbsType>();
		standartTypes.add(new JdbsType("int2", 5));
		standartTypes.add(new JdbsType("int4", 4));
		standartTypes.add(new JdbsType("oid", 4));
		standartTypes.add(new JdbsType("int8", -5));
		standartTypes.add(new JdbsType("cash", 8));
		standartTypes.add(new JdbsType("money", 8));
		standartTypes.add(new JdbsType("numeric", 2));
		standartTypes.add(new JdbsType("float4", 7));
		standartTypes.add(new JdbsType("float8", 8));
		standartTypes.add(new JdbsType("bpchar", 1));
		standartTypes.add(new JdbsType("char", 1));
		standartTypes.add(new JdbsType("char2", 1));
		standartTypes.add(new JdbsType("char4", 1));
		standartTypes.add(new JdbsType("char8", 1));
		standartTypes.add(new JdbsType("char16", 1));
		standartTypes.add(new JdbsType("varchar", 12));
		standartTypes.add(new JdbsType("text", 12));
		standartTypes.add(new JdbsType("name", 12));
		standartTypes.add(new JdbsType("filename", 12));
		standartTypes.add(new JdbsType("bytea", -2));//
		standartTypes.add(new JdbsType("bool", -7));
		standartTypes.add(new JdbsType("bit", -7));
		standartTypes.add(new JdbsType("date", 91));
		standartTypes.add(new JdbsType("time", 92));
		standartTypes.add(new JdbsType("abstime", 93));
		standartTypes.add(new JdbsType("timestamp", 93));
		standartTypes.add(new JdbsType("timestamptz", 93));
		standartTypes.add(new JdbsType("", 1111));
		return standartTypes;
	}
	public static List<JdbsType> getStandardTypes() {
		return standartTypes;
	}
    /**
     * @param param
     * @return String
     */
    @Override
	public String correct(final String param){
    	final String out = param;
    	if(param!=null){
	    	return out.toLowerCase();
    	}
    	return out;
    }    
    
}
